-------------------------------------------------------- Misc -------------------------------------

---------- GetCurrentUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCurrentUser]') and xtype in (N'FN', N'IF',N'TF', N'FS', N'FT'))
	drop function [dbo].[GetCurrentUser]
GO

CREATE FUNCTION [dbo].[GetCurrentUser]()
RETURNS VARCHAR(100)
AS
BEGIN
     DECLARE @sysuser VARCHAR(100)
     SELECT @sysuser = SYSTEM_USER

     IF CHARINDEX('\', @sysuser) > 0
     BEGIN
          SET @sysuser = RIGHT(@sysuser, LEN(@sysuser) - CHARINDEX('\', @sysuser))
     END

     IF CHARINDEX('@', @sysuser) > 0
     BEGIN
          SET @sysuser = LEFT(@sysuser, CHARINDEX('@', @sysuser) - 1)
     END

     RETURN @sysuser
END
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[GetCurrentUser] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- [GetPermittedAgents]
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetPermittedAgents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--	drop procedure [dbo].GetPermittedAgents
--GO

ALTER PROC [dbo].[GetPermittedAgents]
(
     @logonname VARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @reports TABLE (logonname VARCHAR(100))

    IF ((IS_MEMBER('LAKESIDE\IT') = 1) OR (IS_MEMBER('LAKESIDE\ComplaintsCallMonitors') = 1))
    BEGIN
            INSERT INTO @reports SELECT LOGON_NAME FROM ADUsers
    END
    ELSE
    BEGIN
        /* Replaced JKH 24/7/2012 - new Cascade AD update structure */
            INSERT INTO @reports (logonname)
                  /* replaced call id 29819 */
                  SELECT mu.LogonName FROM ManagersAndUsers mu WITH (NOLOCK)
                  INNER JOIN
                  (
                  SELECT cm.Department FROM CallMonitoringDepartments cm WITH (NOLOCK)
                        INNER JOIN ManagersAndUsers m2 WITH (NOLOCK) ON cm.UMID = m2.UMID
                  WHERE m2.LogonName LIKE @logonname
                  ) mx ON mu.Department = mx.Department

                  --SELECT mu.LogonName
                  --FROM ManagersAndUsers mu WITH (NOLOCK)
                  --    INNER JOIN CallMonitoringDepartments cm WITH (NOLOCK) ON mu.Department LIKE cm.Department
                  --    INNER JOIN ManagersAndUsers m2 WITH (NOLOCK) ON cm.UMID = m2.UMID
                  --WHERE m2.LogonName LIKE @logonname

                  /* end replace */
                  --WHERE cm.UMID IN (SELECT UMID FROM ManagersAndUsers WITH (NOLOCK) WHERE LogonName LIKE @logonname)

            INSERT INTO @reports SELECT @logonname

            DECLARE @logonname_org VARCHAR(100) = @logonname

            DECLARE csrReports CURSOR FOR SELECT logonname FROM @reports

            OPEN csrReports
            FETCH NEXT FROM csrReports INTO @logonname

             -- cycle
            WHILE @@FETCH_STATUS = 0
            BEGIN
                  PRINT 'searching for ' + @logonname

                  INSERT INTO @reports SELECT LogonName FROM ManagersAndUsers u INNER JOIN
                          (SELECT [Name] FROM ManagersAndUsers WHERE LogonName = @logonname) m ON u.Manager = m.Name

                  FETCH NEXT FROM csrReports INTO @logonname
            END

            CLOSE csrReports
            DEALLOCATE csrReports

            INSERT INTO @reports
            SELECT a.LOGON_NAME
            FROM
                  CallMonitoringDepartments c WITH (NOLOCK)
                  INNER JOIN ADUsers a WITH (NOLOCK) ON c.UserID = a.OBJECT_GUID
                  INNER JOIN ManagersAndUsers m WITH (NOLOCK) ON c.UMID = m.UMID
            WHERE m.LogonName = @logonname_org
      END

    -- get all back
    SELECT DISTINCT r.logonname, m.GivenName, m.Surname, m.Name, m.Department, a.OBJECT_GUID FROM @reports r
      INNER JOIN ManagersAndUsers m ON r.logonname = m.LogonName
      INNER JOIN ADUsers a ON UPPER(m.LogonName) = UPPER(a.LOGON_NAME)
      ORDER BY m.Surname, m.GivenName

END
GO
-- uncoment for EFS GRANT EXEC ON [dbo].[GetPermittedAgents] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EXEC [dbo].[GetPermittedAgents] 'calinc'




-------------------------------------------------------- Agents -------------------------------------

---------- EventLogs_Agents
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_Agents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[EventLogs_Agents]
GO

CREATE PROCEDURE [dbo].[EventLogs_Agents]
AS
	-- executing user
	DECLARE @sysuser VARCHAR(100)
	SELECT @sysuser = dbo.GetCurrentUser();

	-- create a temporary table to hold the results
	DECLARE @tmpPermittedAgents TABLE (logonname VARCHAR(100), FIRST_NAME VARCHAR(100), LAST_NAME VARCHAR(100), Name VARCHAR(100), Department VARCHAR(100), OBJECT_GUID UNIQUEIDENTIFIER)
	INSERT INTO @tmpPermittedAgents EXEC [dbo].GetPermittedAgents @sysuser;


	SELECT DISTINCT dbo.EventLogs.AGENT_AD_GUID, dbo.ADUsers.FIRST_NAME, dbo.ADUsers.LAST_NAME
	FROM            @tmpPermittedAgents tpa
	INNER JOIN
					dbo.EventLogs ON dbo.EventLogs.AGENT_AD_GUID = tpa.OBJECT_GUID
	INNER JOIN
					dbo.ADUsers ON dbo.ADUsers.OBJECT_GUID = dbo.EventLogs.AGENT_AD_GUID 
	INNER JOIN 
					CC.dbo.Agents ON CC.dbo.Agents.DOMAIN_OID = dbo.EventLogs.AGENT_AD_GUID

GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_Agents] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
exec [dbo].[EventLogs_Agents]
*/
-------------------------------------------------------- EventLogs -------------------------------------


---------- EventLogs_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[EventLogs_Create]
GO

CREATE PROCEDURE [dbo].[EventLogs_Create]
	@occurred datetime,
	@conversation_guid uniqueidentifier,
	@endpoint_id integer,
	@customer_uri varchar(100),
	@agent_ad_guid uniqueidentifier,
	@supervisor_ad_guid uniqueidentifier,
	@event_code integer,
	@hw_party_id bigint,
	@hw_applicant_id integer,
	@conversation_class_id integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[EventLogs]
	(
		[OCCURRED],
		[CONVERSATION_GUID],
		[ENDPOINT_ID],
		[CUSTOMER_URI],
		[AGENT_AD_GUID],
		[SUPERVISOR_AD_GUID],
		[EVENT_CODE],
		[HW_PARTY_ID],
		[HW_APPLICANT_ID],
		[CONVERSATION_CLASS_ID]
	)
	VALUES
	(
		@occurred,
		@conversation_guid,
		@endpoint_id,
		RTRIM(LTRIM(@customer_uri)),
		@agent_ad_guid,
		@supervisor_ad_guid,
		@event_code,
		@hw_party_id,
		@hw_applicant_id,
		@conversation_class_id
	)
	
	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
DECLARE @occurred datetime
SET @occurred = GETDATE()
EXEC [dbo].[EventLogs_Create] @occurred, '{606ebb37-e1a5-4c49-95c9-2f3511b80ce8}', 'cst_uri_1', '{606ebb37-e1a5-4c49-95c9-2f3511b80ce8}', '{606ebb37-e1a5-4c49-95c9-2f3511b80ce8}', 1, 20001, 20002, 45, @id=@id OUTPUT
SELECT @id
*/


---------- EventLogs_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[EventLogs_Delete]
GO

CREATE PROCEDURE [dbo].[EventLogs_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[EventLogs]
	WHERE
		[dbo].[EventLogs].[ID] = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_Delete] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO


-- EventLogs_MonthsOfYear
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_DaysOfMonth]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[EventLogs_DaysOfMonth]
GO

-- create
CREATE PROCEDURE dbo.[EventLogs_DaysOfMonth]
(
	@year int,
	@month int
)
AS
	SELECT 
		DISTINCT DATEPart(day, OCCURRED) AS Day
	FROM dbo.EventLogs
	WHERE 
		DATEPART(year, OCCURRED) = @year
		AND DATEPART(month, OCCURRED) = @month
		ORDER BY Day ASC;
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_DaysOfMonth] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EventLogs_MonthsOfYear
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_MonthsOfYear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[EventLogs_MonthsOfYear]
GO

CREATE PROCEDURE [dbo].[EventLogs_MonthsOfYear]
(
	@year int
)
AS
	SELECT 
		DISTINCT DATENAME(month, OCCURRED) AS Name,
		DATEPART(month, OCCURRED) AS Code
	FROM 
		dbo.EventLogs
	WHERE 
		DATEPART(year, OCCURRED) = @year 
		ORDER BY Code  ASC;
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_MonthsOfYear] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EventLogs_Years
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_Years]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[EventLogs_Years]
GO

CREATE PROCEDURE [dbo].[EventLogs_Years]
AS
	SELECT DISTINCT DATEPART(year, OCCURRED) AS Year
	FROM dbo.EventLogs
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_Years] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EventLogs_Endpoints
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_Endpoints]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[EventLogs_Endpoints]
GO

CREATE PROCEDURE [dbo].[EventLogs_Endpoints]
AS
	SELECT DISTINCT 
		dbo.EventLogs.ENDPOINT_ID, 
		CC.dbo.CustomerEndPoints.TEL
	FROM            
		dbo.EventLogs 
	INNER JOIN                      
		CC.dbo.CustomerEndPoints ON dbo.EventLogs.ENDPOINT_ID = CC.dbo.CustomerEndPoints.ID
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_Endpoints] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
	EXEC [dbo].[EventLogs_Browse] 2011, 9, 15, null, '30db37a7-fcd9-42db-98a2-a92082db9cd5'
*/

---------- EventLogs_Events
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLogs_GetConversationEvents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--	drop procedure [dbo].[EventLogs_GetConversationEvents]
--GO

--CREATE PROCEDURE [dbo].[EventLogs_GetConversationEvents]
--	@conversation_guid uniqueidentifier
--AS

--	select 
--		EventLogs.CONVERSATION_GUID,
--		EventLogs.OCCURRED,
--		EventLogs.EVENT_CODE,
--		UsersAgents.FIRST_NAME as AGENT_FIRST_NAME,
--		UsersAgents.LAST_NAME as AGENT_LAST_NAME,
--		UsersSupervisors.FIRST_NAME as SUPERVISOR_FIRST_NAME,
--		UsersSupervisors.LAST_NAME as SUPERVISOR_LAST_NAME,	
--		EventLogs.CUSTOMER_URI,
--		EventLogs.HW_PARTY_ID,
--		EventLogs.HW_APPLICANT_ID,
--		Socrates_Party.FirstName as FIRST_NAME,
--		Socrates_Party.Surname as LAST_NAME,
--		Classifications.NAME as CONVERSATION_CLASS_NAME		
--	from
--		[dbo].[EventLogs] EventLogs
--	LEFT JOIN
--			[dbo].[ADUsers] UsersAgents ON UsersAgents.OBJECT_GUID = EventLogs.AGENT_AD_GUID
--	LEFT JOIN
--			[dbo].[ADUsers] UsersSupervisors ON UsersSupervisors.OBJECT_GUID = EventLogs.SUPERVISOR_AD_GUID		
--	LEFT JOIN
--		[dbo].[Socrates_Party] Socrates_Party on 
--			(Socrates_Party.TypeID = 'A' AND Socrates_Party.ID = EventLogs.HW_APPLICANT_ID) or 
--			(Socrates_Party.TypeID = 'P' AND Socrates_Party.ID = EventLogs.HW_PARTY_ID)
--	LEFT JOIN
--			[dbo].[Classifications] Classifications ON Classifications.ID = EventLogs.CONVERSATION_CLASS_ID
--	where
--		EventLogs.CONVERSATION_GUID = @conversation_guid
--	order by 	
--		EventLogs.OCCURRED	
--GO

-- uncoment for EFS GRANT EXEC ON [dbo].[EventLogs_GetConversationEvents] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[EventLogs_GetConversationEvents] '606ebb37-e1a5-4c49-95c9-2f3511b80ce8'
*/

----------------------------------------------------- END - EventLogs -----------------------------------


-------------------------------------------------------- UserActivity -------------------------------------


---------- UserActivity_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserActivity_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[UserActivity_Create]
GO

CREATE PROCEDURE [dbo].[UserActivity_Create]
	@occurred datetime,
	@user_ad_guid uniqueidentifier,
	@activity_code bigint,
	@custom varchar(100),
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[UserActivity]
	(
		[OCCURRED],
		[USER_AD_GUID],
		[ACTIVITY_CODE],
		[CUSTOM]
	)
	VALUES
	(
		@occurred,
		@user_ad_guid,
		@activity_code,
		@custom
	)
	
	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[UserActivity_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
DECLARE @occurred datetime
SET @occurred = GETDATE()
EXEC [dbo].[UserActivity_Create] @occurred, '{606ebb37-e1a5-4c49-95c9-2f3511b80ce8}', 'cst_uri_1', '{606ebb37-e1a5-4c49-95c9-2f3511b80ce8}', '{606ebb37-e1a5-4c49-95c9-2f3511b80ce8}', 1, 20001, 20002, 45, @id=@id OUTPUT
SELECT @id
*/

----------------------------------------------------- Start - Surveys -----------------------------------

---------- Surveys_GetAll
-- 0 - show All
-- 1 - show Surveys
-- 2 - show Templates

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_GetAll]
GO

CREATE PROCEDURE [dbo].[Surveys_GetAll]
	@template integer,
	@type integer,
	@reviewType integer
AS
	SELECT
		*
	FROM
		[dbo].[Surveys]
	where 
		ISNULL(DELETED, 0) != 1 AND
		(@type is null or [TYPE] = @type) AND
		(@reviewType is null or [REVIEW_TYPE] = @reviewType) AND
		(@template is null or TEMPLATE = @template)
	ORDER BY
		NAME ASC
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_GetAll] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO


/*
EXEC [dbo].[Surveys_GetAll] null, null, null
*/


---------- Surveys_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_GetByID]
GO

CREATE PROCEDURE [dbo].[Surveys_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[Surveys]
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_GetByID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[Surveys_GetByID] 1
*/


---------- Surveys_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Create]
GO

CREATE PROCEDURE [dbo].[Surveys_Create]
	@name varchar(100),
	@isTemplate integer,
	@type integer,
	@templateID integer,
	@reviewType integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[Surveys]
	(
		[NAME],
		[TYPE],
		[TEMPLATE],
		[REVIEW_TYPE]
	)
	VALUES
	(
		LTRIM(RTRIM(@name)),
		@type,
		@isTemplate,
		@reviewType
	)

	SET @id = SCOPE_IDENTITY();

	-- duplicate data of the template survey
	IF (@templateID > 0)
	BEGIN
		exec Surveys_Duplicate_Categories @templateID, @id;
	END
		
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
EXEC [dbo].[Surveys_Create] 'Direct', @id=@id OUTPUT
SELECT @id
*/


---------- Surveys_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Update]
GO

CREATE PROCEDURE [dbo].[Surveys_Update]
	@id integer,
	@type integer,
	@name varchar(100),
	@reviewType integer
AS
	UPDATE
		[dbo].[Surveys]
	SET
		[NAME] = LTRIM(RTRIM(@name)),
		[TYPE] = @type,
		[REVIEW_TYPE] = @reviewType
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[Surveys_Update] 1, 'Claims'
*/


---------- Surveys_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Delete]
GO

CREATE PROCEDURE [dbo].[Surveys_Delete]
	@id integer
AS
	UPDATE [dbo].[Surveys]
	SET
		DELETED = 1
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_Delete] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[Surveys_Delete] 1
*/

---------- Survey_GetCountNumberOfQuestions
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Survey_GetCountNumberOfQuestions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Survey_GetCountNumberOfQuestions]
GO

CREATE PROCEDURE [dbo].[Survey_GetCountNumberOfQuestions]
	@survey_id integer
AS
	SELECT
		COUNT(SurveyQuestions.ID) as CNT
	FROM
		[dbo].[SurveyQuestions] 
	INNER JOIN
		[dbo].[SurveyCategories] ON SurveyQuestions.SURVEY_CATEGORY_ID = SurveyCategories.ID
	INNER JOIN
		[dbo].[Surveys] ON Surveys.ID = SurveyCategories.SURVEY_ID
	WHERE
		SURVEY_ID = @survey_id 
	AND
		ISNULL(SurveyQuestions.DELETED, 0) != 1
	AND
		ISNULL(SurveyCategories.DELETED, 0) != 1
	AND 
		ISNULL(Surveys.DELETED, 0) != 1
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Survey_GetCountNumberOfQuestions] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- Survey_GetCountNumberOfAnswers
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Survey_GetCountNumberOfAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Survey_GetCountNumberOfAnswers]
GO

CREATE PROCEDURE [dbo].[Survey_GetCountNumberOfAnswers]
	@survey_session_id integer
AS
	SELECT
		COUNT(SurveySessionAnswers.ID) as CNT
	FROM
		[dbo].[SurveySessionAnswers]
	WHERE
		SURVEY_SESSION_ID = @survey_session_id 
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Survey_GetCountNumberOfAnswers] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO


-- exec [dbo].[Survey_GetCountNumberOfAnswers] 68

------[SurveySessions_GetAvailableConversationSurveysForUser]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAvailableConversationSurveysForUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAvailableConversationSurveysForUser]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAvailableConversationSurveysForUser]
	@survey_by varchar(100),
	@conversation_guid uniqueidentifier,
	@reviewType integer
AS
	SELECT
		Surveys.ID,
		Surveys.NAME,
		Surveys.DELETED,
		Surveys.TEMPLATE,
		Surveys.[TYPE],
		Surveys.[REVIEW_TYPE]
	FROM
		[dbo].[Surveys]
	WHERE
		[TYPE] = 0
		AND
		(
			@reviewType is null or [REVIEW_TYPE] = @reviewType
		)
		AND
		Surveys.ID NOT IN
		(
			SELECT 
				SurveySessions.SURVEY_ID
			FROM
				[dbo].[SurveySessions]
			WHERE
				SURVEY_BY = @survey_by
			AND 
				CONVERSATION_GUID = @conversation_guid
		) 
	AND
		ISNULL(DELETED, 0) != 1
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessions_GetAvailableConversationSurveysForUser] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

------[SurveySessions_GetAvailableDocumentsSurveysForUser]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAvailableDocumentsSurveysForUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAvailableDocumentsSurveysForUser]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAvailableDocumentsSurveysForUser]
	@survey_by varchar(100),
	@document_id varchar(20),
	@reviewType integer
AS
	SELECT
		Surveys.ID,
		Surveys.NAME,
		Surveys.DELETED,
		Surveys.TEMPLATE,
		Surveys.[TYPE],
		Surveys.[REVIEW_TYPE]
	FROM
		[dbo].[Surveys]
	WHERE
		[TYPE] = 1
		AND
		(
			@reviewType is null or [REVIEW_TYPE] = @reviewType
		)
		AND
		Surveys.ID NOT IN
		(
			SELECT 
				SurveySessions.SURVEY_ID
			FROM
				[dbo].[SurveySessions]
			WHERE
				SURVEY_BY = @survey_by
			AND 
				DOCUMENT_ID = @document_id
		)
	AND
		ISNULL(DELETED, 0) != 1
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessions_GetAvailableSurveysForUser] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO


-- exec [dbo].[SurveySessions_GetAvailableSurveysForUser] "ESS\Ildikob", "0fac3a34-862d-4a85-b654-811f930ee2af"

-- SurveySessions_GetAllConversationSurveys
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAllConversationSurveys]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAllConversationSurveys]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAllConversationSurveys]
	@conversation_guid uniqueidentifier,
	@reviewType integer
AS
	SELECT
		[dbo].[Surveys].ID as surveyID,
		[dbo].[Surveys].NAME,
		[dbo].[Surveys].TEMPLATE,
		[dbo].[Surveys].[TYPE],
		[dbo].[Surveys].[REVIEW_TYPE],
		[dbo].[SurveySessions].OCCURRED,
		[dbo].[SurveySessions].ID as sessionID,
		[dbo].[SurveySessions].SURVEY_ID,
		[dbo].[SurveySessions].CONVERSATION_GUID,
		[dbo].[SurveySessions].DOCUMENT_ID,
		[dbo].[SurveySessions].SURVEY_STOP,
		[dbo].[SurveySessions].SURVEY_START,
		[dbo].[SurveySessions].SURVEY_CONTINUE,
		[dbo].[SurveySessions].IS_COMPLETED,
		[dbo].[ADUsers].LOGON_NAME,
		[dbo].[ADUsers].FIRST_NAME,
		[dbo].[ADUsers].LAST_NAME
	FROM
		[dbo].[Surveys]
	INNER JOIN
		[dbo].[SurveySessions] ON Surveys.ID = SurveySessions.SURVEY_ID
	INNER JOIN
		[dbo].[ADUsers] ON SurveySessions.SURVEY_BY = ADUsers.LOGON_NAME
	WHERE
		SurveySessions.CONVERSATION_GUID = @conversation_guid
		AND
		(
			@reviewType is null or [REVIEW_TYPE] = @reviewType
		)
		AND
		ISNULL(DELETED, 0) != 1
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessions_GetAllConversationSurveys] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO


-- SurveySessions_GetAllDocumentSurveys
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAllDocumentSurveys]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAllDocumentSurveys]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAllDocumentSurveys]
	@document_id varchar(20),
	@reviewType integer
AS
	SELECT
		[dbo].[Surveys].ID as surveyID,
		[dbo].[Surveys].NAME,
		[dbo].[Surveys].TEMPLATE,
		[dbo].[Surveys].[TYPE],
		[dbo].[Surveys].[REVIEW_TYPE],
		[dbo].[SurveySessions].OCCURRED,
		[dbo].[SurveySessions].ID as sessionID,
		[dbo].[SurveySessions].SURVEY_ID,
		[dbo].[SurveySessions].CONVERSATION_GUID,
		[dbo].[SurveySessions].DOCUMENT_ID,
		[dbo].[SurveySessions].SURVEY_STOP,
		[dbo].[SurveySessions].SURVEY_START,
		[dbo].[SurveySessions].SURVEY_CONTINUE,
		[dbo].[SurveySessions].IS_COMPLETED,
		[dbo].[ADUsers].LOGON_NAME,
		[dbo].[ADUsers].FIRST_NAME,
		[dbo].[ADUsers].LAST_NAME
	FROM
		[dbo].[Surveys]
	INNER JOIN
		[dbo].[SurveySessions] ON Surveys.ID = SurveySessions.SURVEY_ID
	INNER JOIN
		[dbo].[ADUsers] ON SurveySessions.SURVEY_BY = ADUsers.LOGON_NAME
	WHERE
		SurveySessions.DOCUMENT_ID = @document_id
		AND
		(
			@reviewType is null or [REVIEW_TYPE] = @reviewType
		)
		AND
		ISNULL(DELETED, 0) != 1
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessions_GetAllDocumentSurveys] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- select * from [dbo].[SurveySessions] order by OCCURRED desc
-- update [dbo].[SurveySessions] set SURVEY_BY = 'danrm' where SURVEY_BY = 'chrisc' order by OCCURRED desc
-- select * from ADUsers where LOGON_NAME like '%danrm%'
-- exec [dbo].[SurveySessions_GetAllDocumentSurveys] 1
----------------------------------------------------- END - Surveys -------------------------------------





----------------------------------------------------- START - Survey Templates -------------------------------------


--------- Surveys_Duplicate_Categories
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Duplicate_Categories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Duplicate_Categories]
GO

CREATE PROCEDURE [dbo].[Surveys_Duplicate_Categories]
	@sourceSurveyID integer,
	@targetSurveyID integer
AS
	-- create cursor
	DECLARE categories_cursor CURSOR 
		FOR SELECT * FROM SurveyCategories WHERE SURVEY_ID = @sourceSurveyID AND (DELETED is NULL OR DELETED = 0);

	-- variables for category fields
	DECLARE @ID int, @NAME varchar(100), @SURVEY_ID int, @PRIORITY int, @DELETED int, @targetCategoryID int;

	OPEN categories_cursor;
	
	FETCH NEXT FROM categories_cursor
	INTO @ID, @NAME, @SURVEY_ID, @PRIORITY, @DELETED;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- create new category
		EXEC SurveyCategories_Create @NAME,	@targetSurveyID, @PRIORITY,	@targetCategoryID OUT;

		-- copy questions
		EXEC Surveys_Duplicate_Questions @ID, @targetCategoryID;

		PRINT '----------- CALL ---------';
		PRINT @ID;
		PRINT @targetCategoryID;
		PRINT '---------- END CALL ---------';

		-- get the next category
		FETCH NEXT FROM categories_cursor
		INTO @ID, @NAME, @SURVEY_ID, @PRIORITY, @DELETED;
	END

	CLOSE categories_cursor;
	DEALLOCATE categories_cursor;
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_Duplicate_Categories] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

--------- Surveys_Duplicate_Questions
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Duplicate_Questions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Duplicate_Questions]
GO

CREATE PROCEDURE [dbo].[Surveys_Duplicate_Questions]
	@sourceSurveyCategoryID integer,
	@targetSurveyCategoryID integer
AS
	-- create cursor
	DECLARE questions_cursor CURSOR 
		FOR SELECT * FROM SurveyQuestions WHERE SURVEY_CATEGORY_ID = @sourceSurveyCategoryID AND (DELETED is NULL OR DELETED = 0);

	-- variables for question fields
	DECLARE @ID int, @QUESTION varchar(MAX), @SURVEY_CATEGORY_ID int, @PRIORITY int, @DELETED int, @ANSWERS_FLOW_DIRECTION int, @targetQuestionID int;

	OPEN questions_cursor;
	
	FETCH NEXT FROM questions_cursor
	INTO @ID, @QUESTION, @SURVEY_CATEGORY_ID, @PRIORITY, @DELETED, @ANSWERS_FLOW_DIRECTION;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- create new question
		EXEC SurveyQuestions_Create @QUESTION, @targetSurveyCategoryID, @ANSWERS_FLOW_DIRECTION, @PRIORITY, @targetQuestionID OUT;

		-- copy answers
		EXEC Surveys_Duplicate_Answers @ID, @targetQuestionID;

		-- get the next question
		FETCH NEXT FROM questions_cursor
		INTO @ID, @QUESTION, @SURVEY_CATEGORY_ID, @PRIORITY, @DELETED, @ANSWERS_FLOW_DIRECTION;
	END

	CLOSE questions_cursor;
	DEALLOCATE questions_cursor;
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_Duplicate_Questions] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

--------- Surveys_Duplicate_Answers
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Duplicate_Answers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Duplicate_Answers]
GO

CREATE PROCEDURE [dbo].[Surveys_Duplicate_Answers]
	@sourceSurveyQuestionID integer,
	@targetSurveyQuestionID integer
AS
	-- create cursor
	DECLARE answers_cursor CURSOR 
		FOR SELECT * FROM SurveyAnswers WHERE SURVEY_QUESTION_ID = @sourceSurveyQuestionID AND (DELETED is NULL OR DELETED = 0);

	-- variables for answer fields
	DECLARE @ID int, @ANSWER varchar(MAX), @SURVEY_QUESTION_ID int, @SCORE int, @PRIORITY int, @DELETED int, @targetAnswerID int;

	OPEN answers_cursor;
	
	FETCH NEXT FROM answers_cursor
	INTO @ID, @ANSWER, @SURVEY_QUESTION_ID, @SCORE, @PRIORITY, @DELETED;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- create new answer
		EXEC SurveyAnswers_Create @ANSWER, @targetSurveyQuestionID,	@SCORE,	@PRIORITY, @targetAnswerID;

		-- get the next answer
		FETCH NEXT FROM answers_cursor
		INTO @ID, @ANSWER, @SURVEY_QUESTION_ID, @SCORE, @PRIORITY, @DELETED;
	END

	CLOSE answers_cursor;
	DEALLOCATE answers_cursor;
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Surveys_Duplicate_Answers] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

----------------------------------------------------- END - Survey Templates -------------------------------------




------------------------------------------------ Start - SurveyCategories -------------------------------


---------- SurveyCategories_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyCategories_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyCategories_GetByID]
GO

CREATE PROCEDURE [dbo].[SurveyCategories_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[SurveyCategories]
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyCategories_GetByID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyCategories_GetByID] 1
*/


---------- SurveyCategories_GetBySurveyID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyCategories_GetBySurveyID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyCategories_GetBySurveyID]
GO

CREATE PROCEDURE [dbo].[SurveyCategories_GetBySurveyID]
	@survey_id integer
AS
	SELECT
		*
	FROM
		[dbo].[SurveyCategories]
	WHERE
		SURVEY_ID = @survey_id
	AND
		ISNULL(DELETED, 0) != 1
	ORDER BY
		[PRIORITY] ASC, [NAME] ASC
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyCategories_GetBySurveyID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyCategories_GetBySurveyID] 1
*/


---------- SurveyCategories_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyCategories_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyCategories_Create]
GO

CREATE PROCEDURE [dbo].[SurveyCategories_Create]
	@name varchar(100),
	@survey_id integer,
	@priority integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[SurveyCategories]
	(
		[NAME],
		[SURVEY_ID],
		[PRIORITY]
	)
	VALUES
	(
		LTRIM(RTRIM(@name)),
		@survey_id,
		@priority
	)

	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyCategories_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
DECLARE @occurred datetime
SET @occurred = GETDATE()
EXEC [dbo].[SurveyCategories_Create] 'Claims', 2, @id=@id OUTPUT
SELECT @id
*/


---------- SurveyCategories_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyCategories_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyCategories_Update]
GO

CREATE PROCEDURE [dbo].[SurveyCategories_Update]
	@id integer,
	@name varchar(100),
	@survey_id integer,
	@priority integer
AS
	UPDATE
		[dbo].[SurveyCategories]
	SET
		[NAME] = LTRIM(RTRIM(@name)),
		[SURVEY_ID] = @survey_id,
		[PRIORITY] = @priority
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyCategories_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyCategories_Update] 1, 'Claims', 1
*/


---------- SurveyCategories_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyCategories_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyCategories_Delete]
GO

CREATE PROCEDURE [dbo].[SurveyCategories_Delete]
	@id integer
AS
	UPDATE
		[dbo].[SurveyCategories]
	SET 
		DELETED = 1
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyCategories_Delete] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyCategories_Delete] 1
*/
------------------------------------------------- END - SurveyCategories -----------------------------------



------------------------------------------------- Start - SurveyQuestions -------------------------------

---------- SurveyQuestions_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyQuestions_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyQuestions_GetByID]
GO

CREATE PROCEDURE [dbo].[SurveyQuestions_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[SurveyQuestions]
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyQuestions_GetByID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyQuestions_GetByID] 1
*/


---------- SurveyQuestions_GetBySurveyCategoryID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyQuestions_GetBySurveyCategoryID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyQuestions_GetBySurveyCategoryID]
GO

CREATE PROCEDURE [dbo].[SurveyQuestions_GetBySurveyCategoryID]
	@survey_category_id integer
AS
	SELECT
		*
	FROM
		[dbo].[SurveyQuestions]
	WHERE
		SURVEY_CATEGORY_ID = @survey_category_id
	AND
		ISNULL(DELETED, 0) != 1
	ORDER BY
		[PRIORITY] ASC, [QUESTION] ASC
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyQuestions_GetBySurveyCategoryID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyQuestions_GetBySurveyCategoryID] 6
*/


---------- SurveyQuestions_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyQuestions_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyQuestions_Create]
GO

CREATE PROCEDURE [dbo].[SurveyQuestions_Create]
	@question varchar(100),
	@survey_category_id integer,
	@answers_flow_direction integer,
	@priority integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[SurveyQuestions]
	(
		[QUESTION],
		[SURVEY_CATEGORY_ID],
		[ANSWERS_FLOW_DIRECTION],
		[PRIORITY]
	)
	VALUES
	(
		LTRIM(RTRIM(@question)),
		@survey_category_id,
		@answers_flow_direction,
		@priority
	)

	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyQuestions_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
DECLARE @occurred datetime
SET @occurred = GETDATE()
EXEC [dbo].[SurveyQuestions_Create] 'What is your name', @id=@id OUTPUT
SELECT @id
*/


---------- SurveyQuestions_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyQuestions_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyQuestions_Update]
GO

CREATE PROCEDURE [dbo].[SurveyQuestions_Update]
	@id integer,
	@question varchar(100),
	@survey_category_id integer,
	@answers_flow_direction int,
	@priority integer
AS
	UPDATE
		[dbo].[SurveyQuestions]
	SET
		[QUESTION] = LTRIM(RTRIM(@question)),
		[SURVEY_CATEGORY_ID] = @survey_category_id,
		[ANSWERS_FLOW_DIRECTION]=@answers_flow_direction,
		[PRIORITY] = @priority
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyQuestions_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyQuestions_Update] 1, 1, 'another dummy text'
*/


---------- SurveyQuestions_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyQuestions_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyQuestions_Delete]
GO

CREATE PROCEDURE [dbo].[SurveyQuestions_Delete]
	@id integer
AS
	UPDATE
		[dbo].[SurveyQuestions]
	SET
		DELETED = 1
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyQuestions_Delete] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyQuestions_Delete] 1
*/
------------------------------------------------- END - SurveyQuestions -----------------------------------



-------------------------------------------------- START - SurveyAnswers -----------------------------------

---------- SurveyAnswers_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyAnswers_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyAnswers_GetByID]
GO

CREATE PROCEDURE [dbo].[SurveyAnswers_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[SurveyAnswers]
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyAnswers_GetByID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyAnswers_GetByID] 4
*/


---------- SurveyAnswers_GetBySurveyQuestionID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyAnswers_GetBySurveyQuestionID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyAnswers_GetBySurveyQuestionID]
GO

CREATE PROCEDURE [dbo].[SurveyAnswers_GetBySurveyQuestionID]
	@survey_question_id integer
AS
	SELECT
		*
	FROM
		[dbo].[SurveyAnswers]
	WHERE
		SURVEY_QUESTION_ID = @survey_question_id
	AND
		ISNULL(DELETED, 0) != 1
	ORDER BY
		PRIORITY ASC, ANSWER ASC
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyAnswers_GetBySurveyQuestionID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyAnswers_GetBySurveyQuestionID] 1
*/


---------- SurveyAnswers_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyAnswers_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyAnswers_Create]
GO

CREATE PROCEDURE [dbo].[SurveyAnswers_Create]
	@answer varchar(MAX),
	@survey_question_id integer,
	@score integer,
	@priority integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[SurveyAnswers]
	(
		[ANSWER],
		[SURVEY_QUESTION_ID],
		[SCORE],
		[PRIORITY]
	)
	VALUES
	(
		LTRIM(RTRIM(@answer)),
		@survey_question_id,
		@score,
		@priority
	)

	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyAnswers_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
EXEC [dbo].[SurveyAnswers_Create] 'Flaviu', 1, 1, @id=@id OUTPUT
SELECT @id
*/


---------- SurveyAnswers_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyAnswers_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyAnswers_Update]
GO

CREATE PROCEDURE [dbo].[SurveyAnswers_Update]
	@id integer,
	@answer varchar(MAX),
	@survey_question_id integer,
	@score integer,
	@priority integer
AS
	UPDATE
		[dbo].[SurveyAnswers]
	SET
		[ANSWER] = LTRIM(RTRIM(@answer)),
		[SURVEY_QUESTION_ID] = @survey_question_id,
		[SCORE] = @score,
		[PRIORITY] = @priority
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyAnswers_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyAnswers_Update] 1, 'Ana', 1, 1
*/


---------- SurveyAnswers_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyAnswers_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyAnswers_Delete]
GO

CREATE PROCEDURE [dbo].[SurveyAnswers_Delete]
	@id integer
AS
	UPDATE
		[dbo].[SurveyAnswers]
	SET
		DELETED = 1
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyAnswers_Delete] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyAnswers_Delete] 1
*/



---------- SurveyAnswers_DeleteBySurveyQuestionID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyAnswers_DeleteBySurveyQuestionID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveyAnswers_DeleteBySurveyQuestionID]
GO

CREATE PROCEDURE [dbo].[SurveyAnswers_DeleteBySurveyQuestionID]
	@question_id integer
AS
	UPDATE
		[dbo].[SurveyAnswers]
	SET
		DELETED = 1
	WHERE
		[SURVEY_QUESTION_ID] = @question_id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveyAnswers_DeleteBySurveyQuestionID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[SurveyAnswers_DeleteBySurveyQuestionID] 1
*/
------------------------------------------------- END - SurveyAnswers -------------------------------------



---------------------------------------------------- SurveySessions ---------------------------------------

---------- SurveySessions_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_Create]
GO

CREATE PROCEDURE [dbo].[SurveySessions_Create]
	@survey_id integer,
	@conversation_guid uniqueidentifier,
	@document_id varchar(20),
	@survey_stop varchar(MAX),
	@survey_start varchar(MAX),
	@survey_continue varchar(MAX),
	@survey_by varchar(50),	
	@is_completed integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[SurveySessions]
	(
		[SURVEY_ID],
		[CONVERSATION_GUID],
		[DOCUMENT_ID],
		[SURVEY_STOP],
		[SURVEY_START],		
		[SURVEY_CONTINUE],
		[SURVEY_BY],
		[IS_COMPLETED]
	)
	VALUES
	(
		@survey_id,
		@conversation_guid,
		@document_id,
		@survey_stop,
		@survey_start,		
		@survey_continue,
		@survey_by,
		@is_completed
	)
	
	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessions_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
EXEC [dbo].[SurveySessions_Create] 1, '{606ebb37-e1a5-4c49-95c9-2f3511b80ce8}', @id=@id OUTPUT
SELECT @id
*/

---------- SurveySessions_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_Update]
GO

CREATE PROCEDURE [dbo].[SurveySessions_Update]
	@id integer,
	@survey_stop varchar(MAX),
	@survey_start varchar(MAX),
	@survey_continue varchar(MAX),
	@is_completed integer
AS
	UPDATE [dbo].[SurveySessions] SET
		SURVEY_STOP = @survey_stop,
		SURVEY_START = @survey_start,
		SURVEY_CONTINUE = @survey_continue,
		IS_COMPLETED = @is_completed
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessions_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO
-- select * from [dbo].[SurveySessions] where DOCUMENT_ID is not null

---------- SurveySessions_GetCountAgainstConversation
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetCountAgainstConversation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetCountAgainstConversation]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetCountAgainstConversation]
	@survey_id integer,
	@conversation_guid uniqueidentifier
AS
	SELECT
		COUNT(*) as CNT
	FROM
		[dbo].[SurveySessions]
	WHERE
		SURVEY_ID = @survey_id AND CONVERSATION_GUID = @conversation_guid
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessions_GetCountAgainstConversation] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- Trigger [dbo].[InsertUserNameOnSurvey]  
IF OBJECT_ID ('InsertUserNameOnSurvey', 'TR') IS NOT NULL
   DROP TRIGGER InsertUserNameOnSurvey;
GO

CREATE TRIGGER [dbo].[InsertUserNameOnSurvey]
   ON  [dbo].[SurveySessions]
   AFTER INSERT
AS
BEGIN      
      SET NOCOUNT ON;
    BEGIN TRY
    DECLARE @sysuser VARCHAR(50)
    SELECT @sysuser = dbo.GetCurrentUser()

    UPDATE dbo.SurveySessions SET SURVEY_BY = @sysuser
            FROM inserted
            WHERE SurveySessions.ID = inserted.ID
      END TRY
      BEGIN CATCH
            RAISERROR (N'Current User (Survey): %s' , 1, 1, @sysuser) 
      END CATCH
END
go

/*
EXEC [dbo].[SurveySessions_GetCountAgainstConversation] 1, '29dacc52-2308-45f4-bef1-4ac6a3c1e224'
*/

---------- SurveySession_GetAnswers
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySession_GetAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySession_GetAnswers]
GO

CREATE PROCEDURE [dbo].[SurveySession_GetAnswers]
	@survey_session_id integer
AS
	SELECT
		SurveySessionAnswers.ID,
		SurveySessionAnswers.SURVEY_SESSION_ID,
		SurveySessionAnswers.SURVEY_ANSWER_ID,
		SurveySessionAnswers.COMMENT,
		SurveyAnswers.SURVEY_QUESTION_ID
	FROM
		[dbo].[SurveySessionAnswers]
	INNER JOIN
		[dbo].[SurveyAnswers] ON SurveySessionAnswers.SURVEY_ANSWER_ID = SurveyAnswers.ID
	WHERE
		SurveySessionAnswers.SURVEY_SESSION_ID = @survey_session_id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySession_GetAnswers] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- exec [dbo].[SurveySession_GetAnswers] 62

---------- SurveySession_GetAnswerByQuestionId
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySession_GetAnswerByQuestionId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySession_GetAnswerByQuestionId]
GO

CREATE PROCEDURE [dbo].[SurveySession_GetAnswerByQuestionId]
	@survey_session_id integer,
	@answer_id integer
AS
	SELECT 
		SurveySessionAnswers.ID,
		SurveySessionAnswers.SURVEY_SESSION_ID,
		SurveySessionAnswers.SURVEY_ANSWER_ID,
		SurveySessionAnswers.COMMENT,
		SurveyAnswers.SURVEY_QUESTION_ID
	FROM
		[dbo].[SurveySessionAnswers]
	INNER JOIN
		[dbo].[SurveyAnswers] ON SurveySessionAnswers.SURVEY_ANSWER_ID = SurveyAnswers.ID
	WHERE
		SurveyAnswers.SURVEY_QUESTION_ID in
			(SELECT
				SurveyAnswers.SURVEY_QUESTION_ID
			FROM
				SurveyAnswers
			WHERE
				SurveyAnswers.ID = @answer_id)
	AND
		SurveySessionAnswers.SURVEY_SESSION_ID = @survey_session_id		
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySession_GetAnswerByQuestionId] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO


/* exec [dbo].[SurveySession_GetAnswerByQuestionId] 68, 28
 exec [dbo].[SurveySession_GetAnswerByQuestionId] 68, 32
*/



---------- SurveySessions_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_Delete]
GO

CREATE PROCEDURE [dbo].[SurveySessions_Delete]
	@id integer
AS
	BEGIN TRY
		BEGIN TRANSACTION myTran
 
			delete from [dbo].[SurveySessionAnswers] where SURVEY_SESSION_ID = @id;
			delete from [dbo].[SurveySessions] where ID = @id;

		COMMIT TRANSACTION myTran
	END TRY
 
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION myTran --RollBack in case of Error
 
			-- Raise an error with the details of the exception
			DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT
			SELECT @ErrMsg = ERROR_MESSAGE(),
			@ErrSeverity = ERROR_SEVERITY()
 
			RAISERROR(@ErrMsg, @ErrSeverity, 1)
	END CATCH
GO

-- exec [dbo].[SurveySessions_Delete] 70

----------------------------------------------------- END - SurveySessions -----------------------------------



--------------------------------------------------- SurveySessionAnswers ------------------------------------

---------- SurveySessionAnswers_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessionAnswers_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessionAnswers_Create]
GO

CREATE PROCEDURE [dbo].[SurveySessionAnswers_Create]
	@survey_session_id integer,
	@survey_answer_id integer,
	@comment varchar(MAX),
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[SurveySessionAnswers]
	(
		[SURVEY_SESSION_ID],
		[SURVEY_ANSWER_ID],
		[COMMENT]
	)
	VALUES
	(
		@survey_session_id,
		@survey_answer_id,
		LTRIM(RTRIM(@comment))
	)
	
	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessionAnswers_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
EXEC [dbo].[SurveySessionAnswers_Create] 1, 1, 7, @id=@id OUTPUT
SELECT @id
*/

---------- SurveySessionAnswers_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessionAnswers_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessionAnswers_Update]
GO

CREATE PROCEDURE [dbo].[SurveySessionAnswers_Update]
	@survey_answer_id integer,
	@comment varchar(MAX),
	@id integer
AS
	UPDATE [dbo].[SurveySessionAnswers]
	SET
		SURVEY_ANSWER_ID = @survey_answer_id,
		COMMENT = @comment
	WHERE
		ID = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[SurveySessionAnswers_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- exec [dbo].[SurveySessionAnswers_Update] 28, "1111", 


----------------------------------------------------- END - SurveySessionAnswers -----------------------------------

--------------------------------------------------- ADUsers --------------------------------

---------- ADUsers_CreateIfNotExists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ADUsers_CreateIfNotExists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[ADUsers_CreateIfNotExists]
GO

CREATE PROCEDURE [dbo].[ADUsers_CreateIfNotExists]
	@object_guid	uniqueidentifier,
	@logon_name		varchar(100),
	@first_name		varchar(100),
	@last_name		varchar(100)
AS
	IF NOT EXISTS(SELECT * from [dbo].[ADUsers] WHERE [OBJECT_GUID] = @object_guid)
	BEGIN
		INSERT INTO [dbo].[ADUsers]
		(
			[OBJECT_GUID],
			[LOGON_NAME],
			[FIRST_NAME],
			[LAST_NAME]
		)
		VALUES
		(
			@object_guid,
			@logon_name,
			@first_name,
			@last_name
		)
	END
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[ADUsers_CreateIfNotExists] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- ADUsers_GetByLogonName
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ADUsers_GetByLogonName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[ADUsers_GetByLogonName]
GO

CREATE PROCEDURE [dbo].[ADUsers_GetByLogonName]	
	@logon_name		varchar(100)
AS
	Select ADUsers.ID from [dbo].[ADUsers] WHERE LOGON_NAME = @logon_name
			
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[ADUsers_GetByLogonName] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
EXEC [dbo].[ADUsers_CreateIfNotExists] '5B62B7EF-5E15-4898-BFB4-C1F8105B31DA', 'ana', 'ana', 'ana' 
*/


---------------------------------------------------------- Classifications -----------------------------------

---------- Classifications_GetAll
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Classifications_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Classifications_GetAll]
GO

CREATE PROCEDURE [dbo].[Classifications_GetAll]
AS
	SELECT
		*
	FROM
		[dbo].[Classifications]
		LEFT JOIN [CC].[dbo].[CustomerEndPoints] ON [CC].[dbo].[CustomerEndPoints].ID = [dbo].[Classifications].CUSTOMER_ENDPOINT_ID
	ORDER BY
		[NAME] ASC
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Classifications_GetAll] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- exec [dbo].[Classifications_GetAll]

---------- Classifications_GetByLineId
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Classifications_GetByLineId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Classifications_GetByLineId]
GO

CREATE PROCEDURE [dbo].[Classifications_GetByLineId]
(
	@lineId integer
)
AS
	SELECT 
		* 
	FROM 
		[dbo].[Classifications] 
	LEFT JOIN  
		[CC].[dbo].[CustomerEndPoints] ON [CC].[dbo].[CustomerEndPoints].ID = [dbo].[Classifications].CUSTOMER_ENDPOINT_ID 
	where 
		ISNULL([dbo].[Classifications].CUSTOMER_ENDPOINT_ID, @lineId) = @lineId 
	ORDER BY 
		[NAME] ASC 
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Classifications_GetByLineId] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EXEC [dbo].[Classifications_GetByLineId] 1


---------- Classifications_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Classifications_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Classifications_GetByID]
GO

CREATE PROCEDURE [dbo].[Classifications_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[Classifications]
		LEFT JOIN [CC].[dbo].[CustomerEndPoints] ON [CC].[dbo].[CustomerEndPoints].ID = [dbo].[Classifications].CUSTOMER_ENDPOINT_ID
	WHERE
		[dbo].[Classifications].[ID] = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Classifications_GetByID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EXEC [dbo].[Classifications_GetByID] 1


---------- Classifications_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Classifications_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Classifications_Create]
GO

CREATE PROCEDURE [dbo].[Classifications_Create]
	@name varchar(100),
	@customerEndpointID integer,
	@id integer OUTPUT
AS
	INSERT INTO	[dbo].[Classifications]
	(
		[NAME],
		[CUSTOMER_ENDPOINT_ID]
	)
	VALUES
	(
		RTRIM(LTRIM(@name)),
		@customerEndpointID
	)
	
	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Classifications_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

/*
DECLARE @id integer
EXEC [dbo].[Classifications_Create] 'Test1', null , @id=@id OUTPUT
SELECT @id
*/


---------- Classifications_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Classifications_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Classifications_Update]
GO

CREATE PROCEDURE [dbo].[Classifications_Update]
	@id integer,
	@customerEndpointID integer,
	@name varchar(100)
AS
	UPDATE
		[dbo].[Classifications]
	SET
		[NAME] = RTRIM(LTRIM(@name)),
		[CUSTOMER_ENDPOINT_ID] = @customerEndpointID
	WHERE
		[dbo].[Classifications].[ID] = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Classifications_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EXEC [dbo].[Classifications_Update] 1, 'Sales'


---------- Classifications_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Classifications_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Classifications_Delete]
GO

CREATE PROCEDURE [dbo].[Classifications_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[Classifications]
	WHERE
		[dbo].[Classifications].[ID] = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[Classifications_Delete] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-- EXEC [dbo].[Classifications_Delete] 11

------------------------------------------------------- END - Classifications ------------------------------


-------------------------------------------------------HuntGroups----------------------------------------------------------------------------

---------- HuntGroups_GetAll
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HuntGroups_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[HuntGroups_GetAll]
GO

CREATE PROCEDURE [dbo].[HuntGroups_GetAll]
AS
	SELECT
		[dbo].[HuntGroups].*
	FROM
		[dbo].[HuntGroups]
	ORDER BY
		[NAME] ASC
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[HuntGroups_GetAll] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-------HuntGroups_GetByID-------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HuntGroups_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[HuntGroups_GetByID]
GO

CREATE PROCEDURE [dbo].[HuntGroups_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[HuntGroups]
	WHERE
		[dbo].[HuntGroups].[ID] = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[HuntGroups_GetByID] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- HuntGroups_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HuntGroups_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[HuntGroups_Create]
GO

CREATE PROCEDURE [dbo].[HuntGroups_Create]
	@name varchar(100),
	@sip varchar(100),
	@id integer OUTPUT
AS
	INSERT INTO	[dbo].[HuntGroups]
	(
		[NAME],
		[SIP]
	)
	VALUES
	(
		RTRIM(LTRIM(@name)),
		RTRIM(LTRIM(@sip))

	)
	
	SET @id = SCOPE_IDENTITY();
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[HuntGroups_Create] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- HuntGroups_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HuntGroups_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[HuntGroups_Update]
GO

CREATE PROCEDURE [dbo].[HuntGroups_Update]
	@id integer,
	@name varchar(100),
	@sip varchar(100)
AS
	UPDATE
		[dbo].[HuntGroups]
	SET
		[NAME] = RTRIM(LTRIM(@name)),
		[SIP]=   RTRIM(LTRIM(@sip))
	WHERE
		[dbo].[HuntGroups].[ID] = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[HuntGroups_Update] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- HuntGroups_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HuntGroups_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[HuntGroups_Delete]
GO

CREATE PROCEDURE [dbo].[HuntGroups_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[HuntGroups]
	WHERE
		[dbo].[HuntGroups].[ID] = @id
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[HuntGroups_Delete] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

-------------------------------------------------------CONTACT MONITORING report----------------------------------------------------------------------------

---------- [ContactMonitoring_NApercent]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContactMonitoring_NApercent]') and xtype in (N'FN', N'IF',N'TF', N'FS', N'FT'))
	drop function [dbo].[ContactMonitoring_NApercent]
GO
create   FUNCTION [dbo].[ContactMonitoring_NApercent](
	@year integer,
	@month integer	
	)
returns table
as
return
(
	select answered.SURVEY_BY, ( coalesce(answered.score,0)) * 100 / (answered.q_count * 10) as performance_percent from
	(
		SELECT top 100 percent   
			SurveySessions.SURVEY_BY,
			sum( SurveyAnswers.score) as score,
			count( SurveyAnswers.id) as q_count                          
		FROM          
			SurveySessions
		INNER JOIN 
			SurveySessionAnswers ON SurveySessionAnswers.SURVEY_SESSION_ID = SurveySessions.ID 
		INNER JOIN 
			SurveyAnswers on SURVEY_ANSWER_ID = SurveyAnswers.ID where SurveyAnswers.score <> -1 and (YEAR(OCCURRED) = @year) AND (MONTH(OCCURRED) = @month) 
		group by 
			SurveySessions.SURVEY_BY
	) 
	as answered 
)
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[ContactMonitoring_NApercent] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO


---------- ContactMonitoring_CountAnswersOfCategory
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContactMonitoring_CountAnswersOfCategory]') and xtype in (N'FN', N'IF',N'TF', N'FS', N'FT'))
	drop function [dbo].[ContactMonitoring_CountAnswersOfCategory]
GO
create  FUNCTION [dbo].[ContactMonitoring_CountAnswersOfCategory](
	@year integer,
	@month integer,
	@categoryID integer = 8,
	@score_limit_min integer = -1,
	@score_limit_max integer = 10
	)
returns table
as
return
(
	SELECT  
		SurveySessions.SURVEY_BY,
		SurveyAnswers.SCORE ,
		count(SurveyAnswers.SCORE) as scorecount
	FROM         
		SurveyAnswers 
	INNER JOIN
			SurveyQuestions ON SurveyQuestions.ID = SurveyAnswers.SURVEY_QUESTION_ID 
	INNER JOIN
			SurveySessionAnswers ON SurveyAnswers.ID = SurveySessionAnswers.SURVEY_ANSWER_ID 
	INNER JOIN
			SurveySessions ON SurveySessionAnswers.SURVEY_SESSION_ID = SurveySessions.ID
	WHERE     
		(
			SurveyAnswers.SURVEY_QUESTION_ID in 
			(
				SELECT [ID]      
				FROM [dbo].[SurveyQuestions]
				where [SURVEY_CATEGORY_ID] = @categoryID
			)
		)
		and 
		(
			(YEAR(SurveySessions.OCCURRED) = @year) AND (MONTH(SurveySessions.OCCURRED) = @month) 
			and 
			(SurveyAnswers.SCORE >= @score_limit_min and SurveyAnswers.SCORE <= @score_limit_max)
		)
	group by
		SurveySessions.SURVEY_BY, SurveyAnswers.SCORE 
)
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[ContactMonitoring_CountAnswersOfCategory] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- ContactMonitoring_CountAnswers
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContactMonitoring_CountAnswers]') and xtype in (N'FN', N'IF',N'TF', N'FS', N'FT'))
	drop function [dbo].[ContactMonitoring_CountAnswers]
GO
create  FUNCTION [dbo].[ContactMonitoring_CountAnswers](
	@year integer,
	@month integer,
	@questionID integer, -- 10
	@score_limit_min integer = -1,
	@score_limit_max integer = 10,
	@countscore bit,
	@iscomment bit
	)
returns table
as
return
(
	SELECT     
		SurveySessions.SURVEY_BY,
		count(SurveyAnswers.SCORE) as scorecount
	FROM         
		SurveyAnswers 
	INNER JOIN
		SurveyQuestions ON SurveyQuestions.ID = SurveyAnswers.SURVEY_QUESTION_ID 
	INNER JOIN
		SurveySessionAnswers ON SurveyAnswers.ID = SurveySessionAnswers.SURVEY_ANSWER_ID 
	INNER JOIN
		SurveySessions ON SurveySessionAnswers.SURVEY_SESSION_ID = SurveySessions.ID
	WHERE     
		(
			SurveyAnswers.SURVEY_QUESTION_ID = @questionID
		)
		and 
		(
			(YEAR(SurveySessions.OCCURRED) = @year) AND (MONTH(SurveySessions.OCCURRED) = @month) 
		)
		and 
		(
			-- count score
			( (@countscore = 1) and (SurveyAnswers.SCORE >= @score_limit_min and SurveyAnswers.SCORE <= @score_limit_max))
			or
			( (@countscore <> 1) and ( 
			-- count commnet existing
				((@iscomment = 1) and ltrim(rtrim(coalesce(SurveySessionAnswers.comment,''))) <>'' )
				or 
			-- count commnet does not exist
				((@iscomment <> 1) and ltrim(rtrim(coalesce(SurveySessionAnswers.comment,''))) ='' )	
			))
		)
	group by
		SurveySessions.SURVEY_BY
)
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[ContactMonitoring_CountAnswers] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

---------- ContactMonitoring_Reviewer
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContactMonitoring_Reviewer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[ContactMonitoring_Reviewer]
GO

create PROCEDURE [dbo].[ContactMonitoring_Reviewer]
	@year integer,
	@month integer	
AS

	-- create temp table
	select * into [#If_I_Was_Customer] from [ContactMonitoring_CountAnswersOfCategory](@year, @month, 8, 0, 10)

	select 
		review.* , 
		coalesce(DPA1.scorecount ,0)as DPA1,
		coalesce(DPA2.scorecount ,0)as DPA2,
		-- coalesce(CNR.scorecount ,0)
		'x' as CNR,
		coalesce(Exceeds.scorecount ,0) as Exceeds,
		coalesce(Meets.scorecount ,0) as Meets,
		coalesce(Sometimes.scorecount ,0) as Sometimes,
		coalesce(Doesnotmeet.scorecount ,0) as Doesnotmeet,
		Performance.performance_percent,
		coalesce(NoIssues.scorecount ,0) as NoIssues,
		coalesce(Issues.scorecount ,0) as Issues
	from
	(                        	
		SELECT        
			SurveySessions.SURVEY_BY,
			ManagersAndUsers.Name AS UserName, 
			ManagersAndUsers.Department, 
			COUNT(DISTINCT SurveySessions.CONVERSATION_GUID) AS CallsReviewNo, 
			ManagersAndUsers.MONTHLY_BENCHMARK	
		FROM            
			ManagersAndUsers 
		INNER JOIN
			SurveySessions ON ManagersAndUsers.LogonName = SurveySessions.SURVEY_BY
		WHERE        
			(YEAR(SurveySessions.OCCURRED) = @Year) AND (MONTH(SurveySessions.OCCURRED) = @Month)
		GROUP BY 
			SurveySessions.SURVEY_BY, ManagersAndUsers.Name, ManagersAndUsers.Department, ManagersAndUsers.MONTHLY_BENCHMARK
	) as review 
	left join 
		(select * from ContactMonitoring_CountAnswers (@year, @month, 10, 0, 2,1,0)) as DPA1 on DPA1.SURVEY_BY = review.SURVEY_BY  
	left join 
		(select * from ContactMonitoring_CountAnswers (@year, @month, 11, 0, 2,1,0)) as DPA2 on DPA2.SURVEY_BY = review.SURVEY_BY  
	left join 
		#If_I_Was_Customer  as Exceeds on Exceeds.SURVEY_BY = review.SURVEY_BY  and Exceeds.score = 10  
	left join 
		#If_I_Was_Customer as Meets on Meets.SURVEY_BY = review.SURVEY_BY  and Meets.score = 7  
	left join 
		#If_I_Was_Customer as Sometimes on Sometimes.SURVEY_BY = review.SURVEY_BY  and Sometimes.score = 4  
	left join 
		#If_I_Was_Customer as Doesnotmeet on Doesnotmeet.SURVEY_BY = review.SURVEY_BY  and Doesnotmeet.score = 1  
	left join 
		(select * from ContactMonitoring_NApercent(@year, @month) ) as Performance on Performance.SURVEY_BY = review.SURVEY_BY 
	left join 
		(select * from ContactMonitoring_CountAnswers (@year, @month, 31, 0, 2,0,0)) as NoIssues on NoIssues.SURVEY_BY = review.SURVEY_BY  
	left join 
		(select * from ContactMonitoring_CountAnswers (@year, @month, 31, 0, 2,0,1)) as Issues on Issues.SURVEY_BY = review.SURVEY_BY  

	-- drop temp	
	drop table [#If_I_Was_Customer]
GO  

-- uncoment for EFS GRANT EXEC ON [dbo].[ContactMonitoring_Reviewer] TO [DomainUsers], [QrdUser]
-- uncoment for EFS GO

------------------------------------------------------- END - CONTACT MONITORING report------------------------------

----------------------------------------------------- Start - DocumentClasses -----------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[docuDocClasses_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[docuDocClasses_GetAll]
GO

CREATE PROCEDURE [dbo].[docuDocClasses_GetAll]
AS
	SELECT
		*
	FROM
		[Socrates].[dbo].[docuDocClasses]
	ORDER BY
		LetterType ASC
GO

-- uncoment for EFS GRANT EXEC ON [dbo].[docuDocClasses_GetAll] TO [DomainUsers], [QrdUser]

-------------------------------------------------------- QuestionareSessionAnswer -------------------------------------


---------- QuestionareSessionAnswer_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[QuestionareSessionAnswer_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[QuestionareSessionAnswer_Create]
GO

CREATE PROCEDURE [dbo].[QuestionareSessionAnswer_Create]
	@occurred datetime,
	@conversation_guid uniqueidentifier,
	@question_id int,
	@answer_code int,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[QuestionareSessionAnswer]
	(
		[OCCURRED],
		[CONVERSATION_GUID],
		[QUESTION_ID],
		[ANSWER_CODE]
	)
	VALUES
	(
		@occurred,
		@conversation_guid,
		@question_id,
		@answer_code
	)
	
	SET @id = SCOPE_IDENTITY();
GO

EFS GRANT EXEC ON [dbo].[ContactMonitoring_Reviewer] TO [DomainUsers], [QrdUser]
GO

-- declare @id int;
-- EXEC [dbo].[QuestionareSessionAnswer_Create] '2010-01-01', 'c5c5f5bf-1fd5-4131-87ad-7f41107125fd', 1, 1, @id
-- select * from [dbo].[QuestionareSessionAnswer]